-- EXEC sp_search_pos '%', -1, -1, '-1', -1, '2005', 4
/*
	@searchType = 1  -- SEARCH FOR ALL POS
	@searchType = 2  -- SEARCH FOR CHANGE ORDER POS
	@searchType = 3  -- SEARCH FOR PENDING ORDER POS
	@searchType = 4  -- SEARCH FOR CANCELLED ORDER POS
*/
ALTER        PROC sp_search_pos @poNumber VARCHAR(50), @vendorId INT, @po_type INT, @status VARCHAR(50), @processStepId INT, @year VARCHAR(10), @searchType INT
AS
BEGIN
	DECLARE @SQLString NVARCHAR(2000)
	DECLARE @ParamDefinition NVARCHAR(500)
	DECLARE @PurchaseOrder_ID INT
	DECLARE @PurchaseOrder_Number VARCHAR(50)
	DECLARE @PurchaseOrder_Date VARCHAR(50)
	DECLARE @Vendor_ID INT
	DECLARE @Vendor_Name VARCHAR(50)
	DECLARE @PoType INT
	DECLARE @PO_Status VARCHAR(50)
	DECLARE @Valid_From VARCHAR(50)
	DECLARE @Valid_To VARCHAR(50)
	DECLARE @Max_Value NUMERIC
	DECLARE @Count INT

	DECLARE @results TABLE ( PurchaseOrder_Id INT, PONumber VARCHAR(50), Vendor_Name VARCHAR(50), PODate VARCHAR(50), POStatus VARCHAR(50), POType INT, Valid_From VARCHAR(50), Valid_To VARCHAR(50), MAX_Value NUMERIC )
	SET @Count = 0	

	SET @SQLString = N'DECLARE PurchaseOrderCursor CURSOR FOR SELECT PurchaseOrder_Id, PurchaseOrder_No, CONVERT( VARCHAR, PO_Date, 101), Vendor_Id, PO_Status, PurchaseOrder_Type, CONVERT( VARCHAR, PO_Valid_From, 101), CONVERT( VARCHAR, PO_Valid_To,101), PO_Max_Value FROM PurchaseOrder_Table '
	SET @ParamDefinition = N'@Prm_PONumber VARCHAR(50), @Prm_Vendor_ID INT, @Prm_PO_Type INT, @Prm_Status VARCHAR(50), @Prm_ProcessStep_ID INT, @Prm_Year VARCHAR(10)'

	IF @searchType = 2
	BEGIN
		SET @SQLString = @SQLString + ' WHERE PO_Status in ( ''New'', ''Approved'', ''Received'' ) '
		SET @Count = 1
	END
	ELSE IF @searchType = 3
	BEGIN
		SET @SQLString = @SQLString + ' WHERE PO_Status in ( ''New'', ''Approved'', ''Received'' ) '
		SET @Count = 1
	END
	ELSE IF @searchType = 4
	BEGIN
		SET @SQLString = @SQLString + ' WHERE PO_Status in ( ''New'', ''Approved'', ''Received'', ''Cancelled'' ) '
		SET @Count = 1
	END

	IF ( @poNumber IS NOT NULL AND LTRIM(RTRIM( @poNumber ) ) <> '' )
	BEGIN
		IF @Count = 0
		BEGIN
			SET @SQLString = @SQLString + ' WHERE PurchaseOrder_No like  @Prm_PONumber '
			SET @Count = 1
		END
		ELSE
		BEGIN
			SET @SQLString = @SQLString + ' AND PurchaseOrder_No like @Prm_PONumber '
		END
	END

	IF ( @vendorId IS NOT NULL AND @vendorId <> -1 )
	BEGIN
		IF @Count = 0
		BEGIN
			SET @SQLString = @SQLString + ' WHERE Vendor_Id = @Prm_Vendor_ID'
			SET @Count = 1
		END
		ELSE
		BEGIN
			SET @SQLString = @SQLString + ' AND Vendor_Id = @Prm_Vendor_ID'
		END

	END

	IF ( @po_type IS NOT NULL AND @po_type <> -1 )
	BEGIN
		IF @Count = 0
		BEGIN
			SET @SQLString = @SQLString + ' WHERE PurchaseOrder_Type = @Prm_PO_Type'
			SET @Count = 1
		END
		ELSE
		BEGIN
			SET @SQLString = @SQLString + ' AND PurchaseOrder_Type = @Prm_PO_Type'
		END
	END

	IF ( @status IS NOT NULL AND LTRIM(RTRIM( @status ) ) <> '-1' )
	BEGIN
		IF @Count = 0
		BEGIN
			SET @SQLString = @SQLString + ' WHERE PO_Status = @Prm_Status'
			SET @Count = 1
		END
		ELSE
		BEGIN
			SET @SQLString = @SQLString + ' AND PO_Status = @Prm_Status'
		END
	END

	IF ( @year IS NOT NULL AND LTRIM(RTRIM( @year ) ) <> '-1' )
	BEGIN
		
		IF @Count = 0
		BEGIN
			SET @SQLString = @SQLString + ' WHERE YEAR( PO_Date ) = @Prm_Year'
			SET @Count = 1
		END
		ELSE
		BEGIN
			SET @SQLString = @SQLString + ' AND YEAR( PO_Date ) = @Prm_Year'
		END
	END

	SET @SQLString = @SQLString + ' ORDER BY PO_Date, PurchaseOrder_No'


	EXECUTE sp_executesql @SQLString, @ParamDefinition, @Prm_PONumber = @poNumber , @Prm_Vendor_ID = @vendorId, @Prm_PO_Type = @po_type, @Prm_Status = @status, @Prm_ProcessStep_Id = @processStepId, @Prm_Year = @year
	
	OPEN PurchaseOrderCursor
	FETCH NEXT FROM PurchaseOrderCursor INTO @PurchaseOrder_ID, @PurchaseOrder_Number, @PurchaseOrder_Date, @Vendor_ID, @PO_Status, @PoType, @Valid_From, @Valid_To, @Max_Value
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SELECT @Vendor_Name = V_Name FROM VENDOR WHERE Vendor_Id = @Vendor_ID

		IF ( @processStepId IS NOT NULL AND @processStepId <> -1 )
		BEGIN
			SET @Count = 0
			IF @searchType = 2
			BEGIN
				SELECT @Count = COUNT(*) FROM PurchaseOrder_Line_Item_Table WHERE PurchaseOrder_Id = @PurchaseOrder_ID AND ProcessStep_Id = @processStepId AND Status IN ( 'New', 'Approved', 'Received' ) 
			END
			ELSE IF @searchType = 3
			BEGIN
				SELECT @Count = COUNT(*) FROM PurchaseOrder_Line_Item_Table WHERE PurchaseOrder_Id = @PurchaseOrder_ID AND ProcessStep_Id = @processStepId AND Status = 'New'
			END
			ELSE IF @searchType = 4
			BEGIN
				SELECT @Count = COUNT(*) FROM PurchaseOrder_Line_Item_Table WHERE PurchaseOrder_Id = @PurchaseOrder_ID AND ProcessStep_Id = @processStepId AND Status = 'Cancelled'
			END
			ELSE
			BEGIN
				SELECT @Count = COUNT(*) FROM PurchaseOrder_Line_Item_Table WHERE PurchaseOrder_Id = @PurchaseOrder_ID AND ProcessStep_Id = @processStepId
			END
			IF @Count <> 0
			BEGIN
				INSERT INTO @results ( PurchaseOrder_Id, PONumber, Vendor_Name, PODate, POStatus, POType, Valid_From, Valid_To, MAX_Value )
				VALUES ( @PurchaseOrder_ID, @PurchaseOrder_Number, @Vendor_Name, @PurchaseOrder_Date, @PO_Status, @PoType, @Valid_From, @Valid_To, @Max_Value )
			END
		END
 		ELSE IF @PoType <> 1
		BEGIN
			SET @Count = 0
			IF @searchType = 2
			BEGIN
				SELECT @Count = COUNT(*) FROM PurchaseOrder_Line_Item_Table WHERE PurchaseOrder_Id = @PurchaseOrder_ID AND Status IN ( 'New', 'Approved', 'Received' ) 
			END
			ELSE IF @searchType = 3
			BEGIN
				SELECT @Count = COUNT(*) FROM PurchaseOrder_Line_Item_Table WHERE PurchaseOrder_Id = @PurchaseOrder_ID AND Status = 'New'
			END
			ELSE IF @searchType = 4
			BEGIN
				SELECT @Count = COUNT(*) FROM PurchaseOrder_Line_Item_Table WHERE PurchaseOrder_Id = @PurchaseOrder_ID AND Status = 'Cancelled'
			END
			ELSE
			BEGIN
				SELECT @Count = COUNT(*) FROM PurchaseOrder_Line_Item_Table WHERE PurchaseOrder_Id = @PurchaseOrder_ID
			END

			IF @Count <> 0
			BEGIN
				INSERT INTO @results ( PurchaseOrder_Id, PONumber, Vendor_Name, PODate, POStatus, POType, Valid_From, Valid_To, MAX_Value )
				VALUES ( @PurchaseOrder_ID, @PurchaseOrder_Number, @Vendor_Name, @PurchaseOrder_Date, @PO_Status, @PoType, @Valid_From, @Valid_To, @Max_Value )
			END
		END
		ELSE
		BEGIN
			IF @searchType = 3 AND @PO_Status = 'New'
			BEGIN
				INSERT INTO @results ( PurchaseOrder_Id, PONumber, Vendor_Name, PODate, POStatus, POType, Valid_From, Valid_To, MAX_Value )
				VALUES ( @PurchaseOrder_ID, @PurchaseOrder_Number, @Vendor_Name, @PurchaseOrder_Date, @PO_Status, @PoType, @Valid_From, @Valid_To, @Max_Value )
			END
			ELSE IF @searchType <> 3
			BEGIN
				if ( @searchType <> 4 ) or ( @searchType = 4 AND @PO_Status = 'Cancelled' )
				BEGIN
				INSERT INTO @results ( PurchaseOrder_Id, PONumber, Vendor_Name, PODate, POStatus, POType, Valid_From, Valid_To, MAX_Value )
				VALUES ( @PurchaseOrder_ID, @PurchaseOrder_Number, @Vendor_Name, @PurchaseOrder_Date, @PO_Status, @PoType, @Valid_From, @Valid_To, @Max_Value )
				END
			END
		END
		FETCH NEXT FROM PurchaseOrderCursor INTO @PurchaseOrder_ID, @PurchaseOrder_Number, @PurchaseOrder_Date, @Vendor_ID, @PO_Status, @PoType, @Valid_From, @Valid_To, @Max_Value
	END
	CLOSE PurchaseOrderCursor
	DEALLOCATE PurchaseOrderCursor
	
	SELECT * FROM @results
END
